{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "initial_id",
   "metadata": {
    "collapsed": true,
    "ExecuteTime": {
     "end_time": "2024-02-05T12:17:37.662356300Z",
     "start_time": "2024-02-05T12:17:37.654837200Z"
    }
   },
   "outputs": [],
   "source": [
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "outputs": [
    {
     "data": {
      "text/plain": "   order_id        date  customer_id     category  amount\n0         1  2024-01-01          100      Fiction     120\n1         2  2024-01-02          101  Non-Fiction     150\n2         3  2024-01-01          100      Fiction      90\n3         4  2024-01-03          102     Children      80\n4         5  2024-01-02          101      Fiction     110",
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>order_id</th>\n      <th>date</th>\n      <th>customer_id</th>\n      <th>category</th>\n      <th>amount</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>1</td>\n      <td>2024-01-01</td>\n      <td>100</td>\n      <td>Fiction</td>\n      <td>120</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>2</td>\n      <td>2024-01-02</td>\n      <td>101</td>\n      <td>Non-Fiction</td>\n      <td>150</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>3</td>\n      <td>2024-01-01</td>\n      <td>100</td>\n      <td>Fiction</td>\n      <td>90</td>\n    </tr>\n    <tr>\n      <th>3</th>\n      <td>4</td>\n      <td>2024-01-03</td>\n      <td>102</td>\n      <td>Children</td>\n      <td>80</td>\n    </tr>\n    <tr>\n      <th>4</th>\n      <td>5</td>\n      <td>2024-01-02</td>\n      <td>101</td>\n      <td>Fiction</td>\n      <td>110</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "json_data = [\n",
    "    {\"order_id\": 1, \"date\": \"2024-01-01\", \"customer_id\": 100, \"category\": \"Fiction\", \"amount\": 120},\n",
    "    {\"order_id\": 2, \"date\": \"2024-01-02\", \"customer_id\": 101, \"category\": \"Non-Fiction\", \"amount\": 150},\n",
    "    {\"order_id\": 3, \"date\": \"2024-01-01\", \"customer_id\": 100, \"category\": \"Fiction\", \"amount\": 90},\n",
    "    {\"order_id\": 4, \"date\": \"2024-01-03\", \"customer_id\": 102, \"category\": \"Children\", \"amount\": 80},\n",
    "    {\"order_id\": 5, \"date\": \"2024-01-02\", \"customer_id\": 101, \"category\": \"Fiction\", \"amount\": 110}\n",
    "]\n",
    "\n",
    "df = pd.DataFrame(json_data)\n",
    "df"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "end_time": "2024-02-05T12:19:04.210877400Z",
     "start_time": "2024-02-05T12:19:04.185744200Z"
    }
   },
   "id": "b48a0d3addde131c"
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "outputs": [
    {
     "data": {
      "text/plain": "   customer_id  amount\n0          100     210\n1          101     260\n2          102      80",
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>customer_id</th>\n      <th>amount</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>100</td>\n      <td>210</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>101</td>\n      <td>260</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>102</td>\n      <td>80</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "grouped = df.groupby('customer_id')['amount'].sum().reset_index()\n",
    "grouped\n"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "end_time": "2024-02-05T12:19:32.220813100Z",
     "start_time": "2024-02-05T12:19:32.198304700Z"
    }
   },
   "id": "419c1f5d5e85a156"
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "outputs": [
    {
     "data": {
      "text/plain": "   customer_id  amount     name\n0          100     210    Alice\n1          101     260      Bob\n2          102      80  Charlie",
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>customer_id</th>\n      <th>amount</th>\n      <th>name</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>100</td>\n      <td>210</td>\n      <td>Alice</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>101</td>\n      <td>260</td>\n      <td>Bob</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>102</td>\n      <td>80</td>\n      <td>Charlie</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "customer_info = pd.DataFrame({\n",
    "    \"customer_id\": [100, 101, 102],\n",
    "    \"name\": [\"Alice\", \"Bob\", \"Charlie\"]\n",
    "})\n",
    "\n",
    "merged_df = pd.merge(grouped, customer_info, on=\"customer_id\")\n",
    "merged_df"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "end_time": "2024-02-05T12:19:44.416750200Z",
     "start_time": "2024-02-05T12:19:44.403155100Z"
    }
   },
   "id": "5dbccf8d4830ced7"
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "outputs": [
    {
     "data": {
      "text/plain": "category    Children  Fiction  Non-Fiction\ndate                                      \n2024-01-01         0      210            0\n2024-01-02         0      110          150\n2024-01-03        80        0            0",
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th>category</th>\n      <th>Children</th>\n      <th>Fiction</th>\n      <th>Non-Fiction</th>\n    </tr>\n    <tr>\n      <th>date</th>\n      <th></th>\n      <th></th>\n      <th></th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>2024-01-01</th>\n      <td>0</td>\n      <td>210</td>\n      <td>0</td>\n    </tr>\n    <tr>\n      <th>2024-01-02</th>\n      <td>0</td>\n      <td>110</td>\n      <td>150</td>\n    </tr>\n    <tr>\n      <th>2024-01-03</th>\n      <td>80</td>\n      <td>0</td>\n      <td>0</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pivot_table = df.pivot_table(values='amount', index='date', columns='category', aggfunc='sum', fill_value=0)\n",
    "pivot_table"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "end_time": "2024-02-05T12:20:02.927036300Z",
     "start_time": "2024-02-05T12:20:02.901709100Z"
    }
   },
   "id": "ddea9bcc32cb42ef"
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "outputs": [],
   "source": [],
   "metadata": {
    "collapsed": false
   },
   "id": "17ff33807b8b9954"
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 2
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython2",
   "version": "2.7.6"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}
